package a_o2odata_deal.utils.zhibo_zf

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/2/7 10:45
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object modify_zf_zhubo_v3 {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    //conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    //conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    //sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")

   /* val taobao_all_data =
      sqlContext.read.orc("s3a://dws-data/g_data/2019/11/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name")
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/10/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/12/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/9/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/8/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/7/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/6/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/5/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/4/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/3/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/2/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/1/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))



      taobao_all_data.repartition(24).write.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/insertshopname/taobao/before/")

    val tmall_all_data =
      sqlContext.read.orc("s3a://dws-data/g_data/2019/11/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name")
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/10/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/12/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/9/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/8/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/7/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/6/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/5/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/4/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/3/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/2/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))
        .union(sqlContext.read.orc("s3a://dws-data/g_data/2019/1/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name"))

    tmall_all_data.repartition(24).write.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/insertshopname/tmall/before/")*/

   /* sqlContext.read.orc("s3a://dws-data/g_data/2020/1/taobao/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name")
      .repartition(8).write.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/insertshopname/taobao/202001/")

    sqlContext.read.orc("s3a://dws-data/g_data/2020/1/tmall/").selectExpr("userId","shopId","shopName","salesAmount","platformId","name")
      .repartition(8).write.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/insertshopname/tmall/202001/")*/

    //加载天猫全年数据
 sqlContext.read.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/insertshopname/tmall/*/*").createOrReplaceTempView("tm_data")
 ////取出uerrid对应销售额最多的shopId
 val tmall = sqlContext.sql(
   s"""
      |select
      |userId,
      |shopId,
      |shopName,
      |platformId,
      |name
      |from
      |(select
      |*,
      |row_number() over(partition by userId order by cast(shop_sales as double) desc) as rank
      |from
      |(select
      |*,
      |sum(salesAmount) over(partition by shopId ) as shop_sales
      |from tm_data))
      |where rank=1
      """.stripMargin).dropDuplicates("userId")
 //加载淘宝数据
 sqlContext.read.json("s3a://o2o-dataproces-group/panzonghao/zhibo_zf/insertshopname/taobao/*/*").createOrReplaceTempView("tb_data")
 //取出uerrid对应销售额最多的shopId
 val taobao = sqlContext.sql(
   s"""
      |select
      |userId,
      |shopId,
      |shopName,
      |platformId,
      |name
      |from
      |(select
      |*,
      |row_number() over(partition by userId order by cast(shop_sales as double) desc) as rank
      |from
      |(select
      |*,
      |sum(salesAmount) over(partition by shopId ) as shop_sales
      |from tb_data))
      |where rank=1
      """.stripMargin).dropDuplicates("userId")
 taobao.selectExpr("userId","shopId","shopName","platformId","name")
   .unionAll(tmall.selectExpr("userId","shopId","shopName","platformId","name")).createOrReplaceTempView("all")
 sqlContext.sql(
   s"""
      |select
      |*
      |from
      |(select
      |*,
      |row_number() over(partition by userId order by cast(platformId as long) desc) as rank
      |from all)
      |where rank=1
        """.stripMargin).drop("rank").dropDuplicates("userId")
   .createOrReplaceTempView("all_data")

    for (months <- 1 to 1){
      val source_data = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/${months}/zhubo_v2/")
      //println("处理前条数:"+source_data.count())

      source_data.createOrReplaceTempView("source_data")

      val result = sqlContext.sql(
        s"""
           |select
           |t1.*,
           |case when t2.userId is null then t1.shopId else t2.shopId end as shopIds,
           |case when t2.userId is null then t1.shopName else t2.shopName end as shopNames,
           |case when t2.userId is null then t1.company_name else t2.name end as company_names,
           |case when t2.userId is null then t1.platformId else t2.platformId end as platformIds
           |from source_data t1
           |left join
           |all_data t2
           |on t1.anchorId=t2.userId
         """.stripMargin).drop("shopId","shopName","company_name","platformId")
        .withColumnRenamed("shopIds","shopId")
        .withColumnRenamed("shopNames","shopName")
        .withColumnRenamed("company_names","company_name")
        .withColumnRenamed("platformIds","platformId")

      //result.printSchema()
//
      //println("处理后条数:"+result.count())
      //result.where("anchorid='3383081585'").show(false)

      result.repartition(4).write.orc(s"s3a://o2o-dataproces-group/panzonghao/zhibo_zf/2020/${months}/zhubo_v3/")
    }

  }

}
